const express = require('express') //引入express 模块
//const session = require('express-session') //引入express 模块
const app = express()

const ordermap=["id","class","num","name","year","proj_id","proj_name","teacher"]
const mysql = require('mysql');
const conn = mysql.createConnection({
  user: 'root',          //用户名
  password: 'vz5ndl',	//密码
  host: '127.0.0.1',		//主机（默认都是local host）
  database: 'research'       //数据库名
})
//conn.connect(err => {
//  console.log(err, '如果为null 就是连接成功');
//})

app.use(express.static('public'))
app.use(express.json())
app.listen(3000, () => {
  app.all('*', function(req, res, next) {
    res.header("Access-Control-Allow-Origin", "http://127.0.0.1:5173");
    res.header('Access-Control-Allow-Methods', 'PUT, GET, POST, DELETE, OPTIONS');
    res.header("Access-Control-Allow-Headers", "X-Requested-With, mytoken, Content-Type");
    res.header("Access-Control-Allow-Credentials", "true");
    next();
  });
  app.post('/delete', (req, res) => {
    //console.log(req.session.privilege)

    //if(req.session.privilege!=0){
      //res.end('{"you" :"小伙子加个v“Wangruiqi0323”"}')
    //}
    
    let sqlStr = "UPDATE `data` SET valid=0 WHERE id="+req.body.key

    conn.query(sqlStr, (err) => {
      //console.log(err, '如果为null，sql语句执行成功')
    })

    //if(typeof(req.session.first)=="undefined"){
      //req.session.first = 0;
    //}

    res.end('{}')

  })
  app.post('/find', (req, res) => {
    if (req.body.prefech){
      var sql = `SELECT * FROM data where valid=1 ORDER BY id desc LIMIT 20 ;`
    }
    else if(req.body.order==3){
      var sql = `SELECT * FROM data where `+ordermap[req.body.order]+` like '%`+req.body.keyword+`%'`+` and year=`+req.body.year+` ORDER BY `+ordermap[req.body.order]
      //console.log(sql);
    }
    else{
      var sql = `SELECT * FROM data where `+ordermap[req.body.order]+` ='`+req.body.keyword+`'`+` and year=`+req.body.year+` ORDER BY `+ordermap[req.body.order]
      //console.log(sql);
    }
    
    conn.query(sql, (err, results) => {
      //console.log(err)
      res.send(JSON.stringify(results))
    })
  })
  app.post('/login', (req, res) => {
    res.setHeader("Access-Control-Allow-Origin", "*");
    let sql = `select username,privilege from admin where username='`+req.body.uname+`' and passwd='`+req.body.pwd+`';`;
    conn.query(sql, (err, results) => {
      if(JSON.stringify(results)=="[]"){
        res.end('{"username":"未登录","privilege":1}');
      }
      else{
      res.end(JSON.stringify(results[0]))
      }
      //console.log(req.session.privilege)
    })
    
  })
  app.post('/logout', (req, res) => {
    //req.session.privilege=1
    res.end("1");
  })
  app.post('/insert', (req, res) => {
    //if(req.session.privilege!=0){
      //res.end('小伙子加个v“Wangruiqi0323”')
   // }
    let sqlStr = "UPDATE `data` SET valid=0 WHERE id="+req.body.id

    conn.query(sqlStr, (err) => {
      //console.log(err, '如果为null，sql语句执行成功')
    })

    let sqlStr2="INSERT INTO `data` VALUES(NULL,"+req.body.year+","+req.body.class+","+req.body.num+",'"+req.body.name+"','"+req.body.proj_id+"','"+req.body.proj_name+"','"+req.body.teacher+"','"+req.body.leader+"',null,1)"

    
    conn.query(sqlStr2, (err) => {
      //console.log(err, '如果为null，sql语句执行成功')
    })
    res.end('{}')
  })

  app.post('/add', (req, res) => {
    //console.log(req.session.privilege)
    //if(req.session.privilege!=0&req.session.privilege!=2){
    //  res.end('小伙子加个v“Wangruiqi0323”')
    //}
    let sqlStr="INSERT INTO `data` VALUES(NULL,"+req.body.year+","+req.body.class+","+req.body.num+",'"+req.body.name+"','"+req.body.proj_id+"','"+req.body.proj_name+"','"+req.body.teacher+"','"+req.body.leader+"',null,1)"

    
    
    res.end('{}')
  })
})

process.on('uncaughtException', err => {
  console.error(err && err.stack)
});